<?php
class Vnf_Db_Table extends Zend_Db_Table_Abstract {
   protected $_name;
   protected $_primary;

    /**
     * Get number records of table...
     *
     * @return int
     */
    public function getTotalRecords($where = null, $cond = null) {
        $select = Zend_Db_Table_Abstract::select();
        $select->from(array('thistable' => $this->_name), array('cnt' => "count('*')"));
        if ($where) {
            if (is_array($cond))
                $select->where($where, $cond);
            else $select->where($where);
        }

        $result = 0;
        $data = $this->fetchRow($select);
        @$result = $data->cnt;

        return $result;
    }

    /**
     * get records support paging...
     *
     * @return array
     */
    public function getRecords($where = null, $order = null, $count = null, $offset = 0/*, $convertToDTO = true*/)
    {
        $select = Zend_Db_Table_Abstract::select();

        if($where) {
            $select->where($where);
        }

        if($order) {
            $select->order($order);
        }

        if($count)
            $select->limit($count, $offset);
            
        $data = $this->fetchAll($select);

        return $data;
        /*if ($data) {
            if (isset($this->_dto)) {
                if ($convertToDTO == true)
                    return $this->_dto->populate($data);
                else
                    return $data->toArray();
            } else {
                return $data->toArray();
            }
        } else {
            return null;
        }*/
    }


    /**
     * Insert or update multi columns in to DB
     *
     * @param array $cols array("col 1", "col 2", "col 3", "col 4" );
     * @param matrix $data array(arrayData, arrayData, ...)
     */
    public function saveRow($cols, $data) {
        $result = array();
        
        $countColumn = count($cols);
        $prepareStmt = "";
        $sqlPrepareStmt = "";
        $tblName = $this->_name;


        if ($countColumn >  0) {
            $prepareValue = "";
            $updateValue = "";
            foreach ($cols as $value) {
                $prepareValue .= $prepareValue=="" ? "?": ",?";
                $updateValue  .= $updateValue=="" ? "`$value` = VALUES(`$value`)": ",`$value` = VALUES(`$value`)";
            }

            $strvalue = "";
            //foreach ($data as $record) {
//                $strvalue .=  ($strvalue == "" ? "":",") . "('" . implode("','", $record) . "')";
                $recordValue = "";
                foreach ($data as $column) {
//                    echo "/*" . $column . " - {$this->_db->quote($column)}\r\n*/";
//                    $quoteValue = $this->_db->quote($column);
//                    $recordValue .=  ($recordValue == "" ? "":",") . $quoteValue;

                    $strvalue .=  ($strvalue == "" ? "":",") . "{$this->_db->quote($column)}";
                }

                $recordValue = "($recordValue)";

                $prepareStmt .= $prepareStmt=="" ? "($recordValue)" : ",($recordValue)";
            //}
            $sqlPrepareStmt = "INSERT INTO $tblName (`" . implode("`,`", $cols) . "`) VALUES ($strvalue)" .
//            $sqlPrepareStmt = "INSERT INTO $tblName (`" . implode("`,`", $cols) . "`) VALUES $prepareStmt " .
                            " ON DUPLICATE KEY UPDATE $updateValue";

            echo "/*" . $sqlPrepareStmt . "*/";
            die;

            $this->_db->query($sqlPrepareStmt);
            $stmt = $this->getAdapter()->prepare($sqlPrepareStmt);

            $rst = $stmt->execute($result);
            return $rst;
        }
        return null;
    }
    

    /**
     * Insert or update multi columns in to DB
     *
     * @param array $cols array("col 1", "col 2", "col 3", "col 4" );
     * @param matrix $data array(arrayData, arrayData, ...)
     */
    public function saveRows($cols, $data) {
        $result = array();
        foreach ($data as $value) {
            $result = array_merge($result, $value);
        }

        $countColumn = count($cols);
        $prepareStmt = "";
        $sqlPrepareStmt = "";
        $tblName = $this->_name;

//        $cols = array("col 1", "col 2", "col 3", "col 4" );

        if ($countColumn >  0) {
            $prepareValue = "";
            $updateValue = "";
            foreach ($cols as $value) {
                $prepareValue .= $prepareValue=="" ? "?": ",?";
                $updateValue  .= $updateValue=="" ? "`$value` = VALUES(`$value`)": ",`$value` = VALUES(`$value`)";
            }

            $strvalue = "";
            foreach ($data as $record) {
//                $strvalue .=  ($strvalue == "" ? "":",") . "('" . implode("','", $record) . "')";
                $recordValue = "";
                foreach ($record as $column) {
//                    echo "/*" . $column . " - {$this->_db->quote($column)}\r\n*/";
//                    $quoteValue = $this->_db->quote($column);
//                    $recordValue .=  ($recordValue == "" ? "":",") . $quoteValue;

                    $strvalue .=  ($strvalue == "" ? "":",") . "{$this->_db->quote($column)}";
                }

                $recordValue = "($recordValue)";

                $prepareStmt .= $prepareStmt=="" ? "($recordValue)" : ",($recordValue)";
            }
            $sqlPrepareStmt = "INSERT INTO $tblName (`" . implode("`,`", $cols) . "`) VALUES ($strvalue)" .
//            $sqlPrepareStmt = "INSERT INTO $tblName (`" . implode("`,`", $cols) . "`) VALUES $prepareStmt " .
                            " ON DUPLICATE KEY UPDATE $updateValue";

            echo "/*" . $sqlPrepareStmt . "*/";

            $this->_db->query($sqlPrepareStmt);
            $stmt = $this->getAdapter()->prepare($sqlPrepareStmt);

            $rst = $stmt->execute($result);
            return $rst;
        }
        return null;
    }
    
    /**
     * This function use for filter extension
     *
     *
     * @return where string
     */
    public static function buildWhere($filter) {
    
        $where = " 0 = 0 ";
        $qs = "";
        if (is_array($filter)) {
            for ($i=0;$i<count($filter);$i++){
                switch($filter[$i]['data']['type']){
                    case 'string' : $qs .= " AND thistable.".$filter[$i]['field']." LIKE '%".$filter[$i]['data']['value']."%'"; break;
                    case 'list' :
                        if (strstr($filter[$i]['data']['value'],',')){
                            $fi = explode(',',$filter[$i]['data']['value']);
                            for ($q=0;$q<count($fi);$q++){
                                $fi[$q] = "'".$fi[$q]."'";
                            }
                            $filter[$i]['data']['value'] = implode(',',$fi);
                            $qs .= " AND thistable.".$filter[$i]['field']." IN (".$filter[$i]['data']['value'].")";
                        }else{
                            $qs .= " AND thistable.".$filter[$i]['field']." = '".$filter[$i]['data']['value']."'";
                        }
                    break;
                    case 'boolean' : $qs .= " AND thistable.".$filter[$i]['field']." = ".($filter[$i]['data']['value']); break;
                    case 'numeric' :
                        switch ($filter[$i]['data']['comparison']) {
                            case 'ne' : $qs .= " AND thistable.".$filter[$i]['field']." != ".$filter[$i]['data']['value']; break;
                            case 'eq' : $qs .= " AND thistable.".$filter[$i]['field']." = ".$filter[$i]['data']['value']; break;
                            case 'lt' : $qs .= " AND thistable.".$filter[$i]['field']." < ".$filter[$i]['data']['value']; break;
                            case 'gt' : $qs .= " AND thistable.".$filter[$i]['field']." > ".$filter[$i]['data']['value']; break;
                        }
                    break;
                    case 'date' :
                        switch ($filter[$i]['data']['comparison']) {
                            case 'ne' : $qs .= " AND thistable.".$filter[$i]['field']." != '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; break;
                            case 'eq' : $qs .= " AND thistable.".$filter[$i]['field']." = '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; break;
                            case 'lt' : $qs .= " AND thistable.".$filter[$i]['field']." < '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; break;
                            case 'gt' : $qs .= " AND thistable.".$filter[$i]['field']." > '".date('Y-m-d',strtotime($filter[$i]['data']['value']))."'"; break;
                        }
                    break;
                }
            }
            $where .= $qs;
            
        
        }    
        return $where;
    }
    
    public function increaseValue($fieldName, $value = 1, $where = null) {
        $tblName = $this->_name;
        $sqlPrepareStmt = "UPDATE $tblName SET $fieldName = $fieldName + $value WHERE $where";

        $this->_db->query($sqlPrepareStmt);
    }
    
    public function concatField($fieldName, $value = '', $where = null) {
        $tblName = $this->_name;
        $sqlPrepareStmt = "UPDATE $tblName SET $fieldName = concat($fieldName ,$value) WHERE $where";

        $this->_db->query($sqlPrepareStmt);
    }
}